package com.loongtech.bi.advertisment.controller;

import com.loongtech.bi.advertisment.service.AdvUserDataService;
import com.loongtech.bi.manager.flow.HadoopQueryManager;
import com.loongtech.bi.manager.system.SysLogManager;
import com.loongtech.core.util.Time;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.Scope;
import org.springframework.stereotype.Controller;

import javax.annotation.PostConstruct;
import javax.annotation.Resource;
import javax.faces.application.FacesMessage;
import javax.faces.context.FacesContext;
import java.io.Serializable;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map.Entry;

/**
 * ClassName: AdvUserLevelDataAction
 *
 * @author liuchen
 * @Description: TODO 广告账号等级统计
 * @date 2017-6-22 上午11:40:41
 */
@Controller("advUserLevelDataAction")
@Scope("view")
public class AdvUserLevelDataAction extends BaseAdvAction {
	private static final long serialVersionUID = 1L;

    private String dateRadio = "7"; // 切换的天数：1=昨天。7=近7天。15=近15天。30=近30天，0=自选时间

    private Date dateStart = new Date(Time.getBeginningOfDay(Time.currentTime() - 7 * Time.DAY)); // 自选时间：开始时间

    private Date dateEnd = new Date(Time.getBeginningOfDay(Time.currentTime() - Time.DAY)); // 自选时间：结束时间

    private Date compareDate = new Date(Time.getBeginningOfDay(Time.currentTime() - Time.DAY)); // 前端传过来的查看时间

    @Resource(name = "advUserDataService")
    private AdvUserDataService service;

    @Autowired
    HadoopQueryManager hadoopQueryManager;

    @Autowired
    private SysLogManager sysLogManager;

    private List<ColumnModel> columns;

    private List<ColumnModel> columnmacs;

    private int maxlevel = 20;

    private List<Object[]> accounts; // 结果List

    private List<Object[]> macs; // 结果List

    @PostConstruct
    public void init() {
        getVipColumns();
        try {
            search();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    private void getVipColumns() {
        columns = new ArrayList<ColumnModel>();
        columnmacs = new ArrayList<ColumnModel>();
        maxlevel = 0;
        try {
            maxlevel = service.getVipLevel(2);
        } catch (Exception e) {
            maxlevel = 15;
            e.printStackTrace();
        }
        columns.add(new ColumnModel("渠道", "0"));
        columnmacs.add(new ColumnModel("渠道", "0"));
        columns.add(new ColumnModel("新增账号", "1"));
        columnmacs.add(new ColumnModel("新增设备", "1"));
        for (int i = 0; i <= maxlevel; i++) {
            columns.add(new ColumnModel(i + 1 + "级", i + 2 + ""));
            columnmacs.add(new ColumnModel(i + 1 + "级", i + 2 + ""));
        }
    }

    public void search() throws Exception {
        sysLogManager.addLog(session.getUserName(), "市场数据分析 —— 等级统计");

        String startDay = Time.getDateStr(new Date(Time.getBeginningOfDay(("0".equals(dateRadio) ? dateStart
                : new Date(Time.getBeginningOfDay(Time.currentTime() - (Integer.parseInt(dateRadio)) * Time.DAY)))
                .getTime()))); // 开始时间
        String endDay = Time.getDateStr(new Date(Time.getBeginningOfDay(("0".equals(dateRadio) ? dateEnd : new Date(
                Time.getBeginningOfDay(Time.currentTime() - Time.DAY))).getTime()))); // 结束时间

        accounts = new ArrayList<>();
        macs = new ArrayList<>();

        FacesContext context = FacesContext.getCurrentInstance();
        if (hasAgents == null || hasAgents.size() <= 0) {
            context.addMessage(null, new FacesMessage("提示", "此用户无任何代理商权限，请联系管理员配置!"));
            return;
        }
        if ((selectedAgent == null || selectedAgent.length <= 0) && !"admin".equals(session.getUserName())) {
            context.addMessage(null, new FacesMessage("提示", "请选择代理商!"));
            return;
        }
        if (HadoopQueryManager.VISIT_QUEUE.offer("AdvUserLevelDataAction")) {
            System.out.println("VISIT_QUEUE offer AdvUserLevelDataAction ok!");
            // ========================账号统计sql===================================
            StringBuffer sql = new StringBuffer();
            sql.append("select a.cname channelName,count(a.userid) newAccount  ");
            sql.append(getColumnSelect("userid"));
            sql.append("from  ");
            sql.append("(select min(nvl(bi.channelname,t.url))cname,userid from bi_adv_totalusercreate t ");
            sql.append("left join bi_advconfig bi on t.url = bi.id ");
            sql.append("where dt = (select max(dt) from bi_adv_totalusercreate) and t.createday >= '" + startDay
                    + "' and t.createday <= '" + endDay + "' ");
            if (selectedChannelName != null && selectedChannelName.length != 0) {
                sql.append("and channelName in (" + getParams(selectedChannelName) + ")");
            }
            if (selectedAgent != null && selectedAgent.length != 0) {
                sql.append("and agent in (" + getParams(selectedAgent) + ")");
            }
            if (selectedPlatform != null && selectedPlatform.length != 0) {
                sql.append("and platform in (" + getParams(selectedPlatform) + ")");
            }
            if (selectedPaytype != null && selectedPaytype.length != 0) {
                sql.append("and paytype in (" + getParams(selectedPaytype) + ")");
            }
            sql.append("group by userid)a ");
            sql.append("left join (select userid,max(rolelevel)rolelevel from roleinfo where dt = '"
                    + Time.getDateStr(compareDate) + "' group by userid)b on a.userid = b.userid ");
            sql.append("group by a.cname ");
            sql.append("order by newAccount ");
            // ========================账号统计sql结束=================================
            // ========================mac统计sql====================================
            StringBuffer sqlmac = new StringBuffer();
            sqlmac.append("select a.cname channelName,count(a.mac) newMac  ");
            sqlmac.append(getColumnSelect("mac"));
            sqlmac.append("from  ");
            sqlmac.append("(select min(nvl(bi.channelname,t.url))cname,mac from bi_adv_totalmaccreate t ");
            sqlmac.append("left join bi_advconfig bi on t.url = bi.id ");
            sqlmac.append("where dt = (select max(dt) from bi_adv_totalmaccreate) and t.createday >= '" + startDay
                    + "' and t.createday <= '" + endDay + "' ");
            if (selectedChannelName != null && selectedChannelName.length != 0) {
                sqlmac.append("and channelName in (" + getParams(selectedChannelName) + ")");
            }
            if (selectedAgent != null && selectedAgent.length != 0) {
                sqlmac.append("and agent in (" + getParams(selectedAgent) + ")");
            }
            if (selectedPlatform != null && selectedPlatform.length != 0) {
                sqlmac.append("and platform in (" + getParams(selectedPlatform) + ")");
            }
            if (selectedPaytype != null && selectedPaytype.length != 0) {
                sqlmac.append("and paytype in (" + getParams(selectedPaytype) + ")");
            }
            sqlmac.append("group by mac)a ");
            sqlmac.append("left join (select a.mac mac,max(a.rolelevel)rolelevel from ");
            sqlmac.append("(select mac,rolelevel from rolelogin where dt='" + Time.getDateStr(compareDate) + "' ");
            sqlmac.append("union all select mac,rolelevel from rolelogout where dt='" + Time.getDateStr(compareDate)
                    + "' ");
            sqlmac.append("union all select mac,rolelevel from userheartbeat where dt='" + Time.getDateStr(compareDate)
                    + "' ");
            sqlmac.append("union all select mac,afterlevel as rolelevel from rolelevelup where dt='"
                    + Time.getDateStr(compareDate) + "')a ");
            sqlmac.append("group by a.mac)b on a.mac = b.mac ");
            sqlmac.append("group by a.cname ");
            sqlmac.append("order by newMac ");
            // ========================mac统计结束sql=================================

            Entry<List<String>, List<Object[]>> ret = hadoopQueryManager.executeSQL(sql.toString());
            Entry<List<String>, List<Object[]>> retmac = hadoopQueryManager.executeSQL(sqlmac.toString());
            if ((null == ret || null == ret.getKey() || ret.getKey().isEmpty() || null == ret.getValue())
                    || (null == retmac || null == retmac.getKey() || retmac.getKey().isEmpty() || null == retmac
                    .getValue())) {
                context.addMessage(null, new FacesMessage("提示", "查询结果为空!"));
                return;
            }
            accounts = changeNum(ret.getValue());
            macs = changeNum(retmac.getValue());

        } else {
            System.out.println("VISIT_QUEUE offer AdvUserLevelDataAction failed!");
            context.addMessage(null, new FacesMessage("提示", "当前系统正被访问，为保证服务器稳定，请稍后再试"));
            return;
        }
    }


    private List<Object[]> changeNum(List<Object[]> re) {
        List<Object[]> r = new ArrayList<Object[]>();
        for (Object[] o : re) {
            Object[] oi = new Object[o.length];
            for (int i = 0; i < o.length; i++) {
                if (i < 1) {
                    oi[i] = o[i];
                } else {
                    oi[i] = Integer.valueOf(((String) o[i]));
                }
            }
            r.add(oi);
        }
        return r;
    }

    private String getColumnSelect(String sel) {
        StringBuilder s = new StringBuilder();
        for (int i = 0; i <= maxlevel; i++) {
            s.append(" ,count(case when b.rolelevel = " + (i + 1) + " then a." + sel + "  end) as v" + (i + 1) + " ");
        }
        return s.toString();
    }

    private String getParams(String[] param) throws Exception {
        StringBuilder s = new StringBuilder();
        for (String str : param) {
            s.append("'" + str + "',");
        }
        return s.substring(0, s.length() - 1);
    }

    public List<Object[]> getAccounts() {
        return accounts;
    }

    public List<ColumnModel> getColumnmacs() {
        return columnmacs;
    }

    public void setColumnmacs(List<ColumnModel> columnmacs) {
        this.columnmacs = columnmacs;
    }

    public void setAccounts(List<Object[]> accounts) {
        this.accounts = accounts;
    }

    public List<Object[]> getMacs() {
        return macs;
    }

    public void setMacs(List<Object[]> macs) {
        this.macs = macs;
    }

    public String getDateRadio() {
        return dateRadio;
    }

    public void setDateRadio(String dateRadio) {
        this.dateRadio = dateRadio;
    }

    public Date getDateStart() {
        return dateStart;
    }

    public void setDateStart(Date dateStart) {
        this.dateStart = dateStart;
    }

    public Date getDateEnd() {
        return dateEnd;
    }

    public void setDateEnd(Date dateEnd) {
        this.dateEnd = dateEnd;
    }

    public List<ColumnModel> getColumns() {
        return columns;
    }

    public void setColumns(List<ColumnModel> columns) {
        this.columns = columns;
    }

    public Date getCompareDate() {
        return compareDate;
    }

    public void setCompareDate(Date compareDate) {
        this.compareDate = compareDate;
    }

    public class ColumnModel implements Serializable {

        private static final long serialVersionUID = 1L;
        private String header;
        private String property;

        public ColumnModel(String header, String property) {
            this.header = header;
            this.property = property;
        }

        public String getHeader() {
            return header;
        }

        public String getProperty() {
            return property;
        }
    }
}
